create database Inventory
GO
use Inventory
GO
create table Roles(
	RoleID varchar(5) primary key,
	RoleName varchar(20) NOT NULL
)
create table Customers
(
	CustomerID int identity(1,1) primary key,
	CustomerName nvarchar(50) NOT NULL,
	Address nvarchar(MAX) NOT NULL,
	Phone varchar(20) NOT NULL,
	Email varchar(50) NOT NULL,
	Gender bit
)
GO
create table Account(
	AccountID int identity(1,1) primary key,
	Username varchar(30) NOT NULL,
	Password varchar(MAX) NOT NULL,
	FullName nvarchar(50) NOT NULL,
	DayOfBirth datetime NOT NULL,
	Gender bit NOT NULL,
	Address varchar(max),
	Phone nvarchar(20),
	Email nvarchar(30),
	RoleID varchar(5) NOT NULL,
	Active bit DEFAULT(1) NOT NULL ,
	ChangedPass bit DEFAULT(0) NOT NULL,
)
GO
create table Categories(
	CategoryID int identity(1,1) primary key,
	CategoryName nvarchar(30) NOT NULL,
	Active bit DEFAULT(1),
)
GO
Create table Items(
	ItemID int identity(1,1) primary key,
	CategoryID int NOT NULL,
	ItemName nvarchar(30) NOT NULL,
	QuantityAvailable int default(0) NOT NULL,
	Price float NOT NULL,
	Unit varchar(20),
	Descript nvarchar(MAX),
	Manufacture varchar(30),
	Status bit DEFAULT(1),
)
GO
create table Bills(
	BillID int identity(1,1) primary key,
	AccountID int NOT NULL,
	CustomerID int NOT NULL,
	Date datetime NOT NULL default(getDate()),
	Discount float,
	Payment varchar(20),
	Status bit DEFAULT(1)
)
GO
create table BillSellDetails
(
	BillID int NOT NULL,
	ItemID int NOT NULL,
	Quantity int NOT NULL,
	QuantityReceipt int default(0)
)
GO
create table Receipt(
	ReceiptID int identity(1,1) primary key,
	BillID int NOT NULL,
	date datetime default(getDate()),
	valid bit default(0),
	accept bit
)
GO
create table Report(
	ReportID int identity(1,1) NOT NULL,
	fromSales int NOT NULL,
	ToManager int NOT NULL,
	dateReport datetime NOT NULL default(getDate()),
	Title nvarchar(MAX),
	Content nvarchar(MAX),
	status bit default(0)
)

GO
alter table Account
add constraint FK_Role_ID foreign key(RoleID) references Roles(RoleID)
GO
alter table Items 
add constraint FK_Item_ID foreign key(CategoryID) references Categories(CategoryID)
GO
alter table BillSellDetails
add constraint FK_Item_ID_Bill foreign key(ItemID)references Items(ItemID)
GO
alter table BillSellDetails
add constraint FK_Bill_ID_Details foreign key(BillID)references Bills(BillID)
GO
Alter table Bills
add constraint FK_Customer_ID foreign key(CustomerID)references Customers(CustomerID)
GO
Alter table Bills
add constraint FK_Account_ID foreign key(AccountID)references Account(AccountID)
go
Alter table Receipt 
add constraint FK_Bill_ID_Receipt foreign key(BillID) references Bills(BillID)
GO
Alter table Report 
add constraint FK_RP_FROM foreign key(fromSales) REFERENCES Account(AccountID)
GO
ALTER TABLE Report
add CONSTRAINT FK_RP_TO FOREIGN key(ToManager) REFERENCES Account(AccountID)

